home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Delphi Developer's Kit 1996
/
Delphi Developer's Kit 1996.iso
/
power
/
dataconv
/
dbftosql.pas
< prev
next >
Wrap
Pascal/Delphi Source File
|
1995-12-22
|
11KB
|
415 lines
{ DBFtoSQL - 'Fixed up' by: Pete Ness
scream@primenet.com
CID: 102347,710
This program was not originally written by me, I got it's raw state
from CIS - user id # 71073,2552 (I'll fill in the name when I get it).
It wasn't quite what I was looking for, but had some potential. I've
re-done just about everything since, but the original author deserve
credit for the original work - the BatchMove component started as his
idea.
The original author used this to import DBF's to Interbase SQL. There
are really no utilities out there that I've found to do this well -
thus I wrote my own. I use Microsoft SQL 6.0 and needed to import
data from Foxpro files. This was originally written to go from
DBF's to SQL only, but it should work to move data from any ODBC
source to any other ODBC source (through the BDE). I'm still having
some problems with SQL 6.0 blocking the inserts after the first 75 -
150 records. If anyone has this problem with this, let me know and
we'll figure out how to fix it. I will also upload a fixed version
when I find the problem.}
unit DbfToSQL;
interface
uses
SysUtils, WinTypes, WinProcs, Messages, Classes, Graphics, Controls,
Forms, Dialogs, MemoForm, StdCtrls, DB, DBTables, Buttons, Gauges, ProgForm,
ExtCtrls, paredit;
type
TDBF2SQL = class(TForm)
CnvrtBtn: TButton;
ExitBtn: TButton;
SrcTable: TTable;
SrcData: TDataSource;
BatchMove: TBatchMove;
DestTable: TTable;
GroupBox1: TGroupBox;
Label1: TLabel;
SourcePath: TEdit;
DbfFile: TLabel;
GroupBox2: TGroupBox;
Label4: TLabel;
Label6: TLabel;
RadioGroup1: TRadioGroup;
Label2: TLabel;
DestPath: TEdit;
Label3: TLabel;
SourceAlias: TComboBox;
DestAlias: TComboBox;
SourceTableName: TComboBox;
DestTableName: TComboBox;
SourceDataBase: TDatabase;
DestDataBase: TDatabase;
SpeedButton1: TSpeedButton;
SpeedButton2: TSpeedButton;
procedure CnvrtBtnClick(Sender: TObject);
procedure ExitBtnClick(Sender: TObject);
procedure SourceTableNameChange(Sender: TObject);
procedure RadioGroup1Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure SourceAliasChange(Sender: TObject);
procedure DestAliasChange(Sender: TObject);
procedure SourceTableNameEnter(Sender: TObject);
procedure DestTableNameEnter(Sender: TObject);
procedure SpeedButton1Click(Sender: TObject);
procedure SpeedButton2Click(Sender: TObject);
private
{ Private declarations }
Procedure OpenSource;
Procedure SetColumnNames;
public
abort : Boolean;
{ Public declarations }
end;
var
DBF2SQL: TDBF2SQL;
lastsourcealias, lastdestalias : string;
implementation
{$R *.DFM}
procedure TDBF2SQL.ExitBtnClick(Sender: TObject);
begin
Dbf2Sql.Close
end;
Procedure TDBF2SQL.OpenSource;
var text : string;
begin
SrcTable.Close;
SourceDataBase.Connected := False;
If (SourceTableName.Text = '')
Then raise Exception.Create('Source Table Name is incomplete!');
if (SourceAlias.Text = '')
Then raise Exception.Create('Source Alias not specified!');
{ The order these are listed in the component is not the same as
the ORD() values therefore, this case is used instead of just
TBatchMode[...ItemIndex] }
Case RadioGroup1.ItemIndex of
0 : BatchMove.Mode := batAppend;
1 : BatchMove.Mode := batAppendUpdate;
2 : BatchMove.Mode := batCopy;
3 : BatchMove.Mode := batDelete;
4 : BatchMove.Mode := batUpdate;
end;
SrcTable.Active := False;
SourceDataBase.AliasName := SourceAlias.Text;
SrcTable.TableName := ExtractFileName(SourceTableName.Text);
if SourcePath.Enabled and (sourcepath.text > '') then
SourceDataBase.Params.Add('PATH='+SourcePath.Text);
try
chdir( SourcePath.Text );
except
Application.HandleException( self );
end;
SrcTable.Open;
SourceDataBase.Connected := True;
end;
{ Read the column names from the database. Be aware that many "NORMAL"
DBF type field names are illegal under sequal. I opted to not
automatically rename the fields as most of the time they are OK. }
Procedure TDBF2SQL.SetColumnNames;
Var MovFlds: TStringList;
I: integer;
SameDBF: Boolean;
Nm: String;
Begin
MovFlds := TStringList.Create;
If SrcTable.FieldCount > 0 then begin
If FieldAssign.ConvFields.Lines.Count > 0
Then SameDBF :=
(Pos(SrcTable.Fields[0].FieldName,FieldAssign.ConvFields.Lines[0])>0)
Else SameDBF := FALSE;
if SameDBF then
MovFlds.Assign(FieldAssign.ConvFields.Lines)
else
For I := 0 to SrcTable.FieldCount-1 do begin
Nm := SrcTable.Fields[I].FieldName;
MovFlds.Add(Nm+'='+Nm);
End;
End Else
raise Exception.Create('No Source Fields Found.');
FieldAssign.ConvFields.Lines.Clear;
FieldAssign.ConvFields.Lines := MovFlds;
if FieldAssign.ShowModal = mrCancel then
Abort := True
else
Abort := False;
MovFlds.Free;
End;
procedure TDBF2SQL.CnvrtBtnClick(Sender: TObject);
var lastcount, ProcCount, ChangeCount, KeyCount, ConvCount : longint;
text : string;
Begin
{ Make sure the proper paths are setup in the parameters - just in
case the path was entered AFTER the table name field }
SourceTableNameEnter(self);
DestTableNameEnter(self);
OpenSource;
SrcTable.First;
If SrcTable.EOF then
raise Exception.Create('Source Table is Empty');
If DestTableName.Text = EmptyStr Then
raise Exception.Create('Destination Tablename is empty');
If DestAlias.Text = EmptyStr Then
raise Exception.Create('Destination Alias is empty');
Abort := False;
SetColumnNames;
End;
procedure TDBF2SQL.SourceTableNameChange(Sender: TObject);
begin
FieldAssign.ConvFields.Lines.Clear
end;
procedure TDBF2SQL.RadioGroup1Click(Sender: TObject);
begin
BatchMove.Mode := TBatchMode(RadioGroup1.ItemIndex);
end;
procedure TDBF2SQL.FormCreate(Sender: TObject);
begin
Session.GetAliasNames(DestAlias.Items);
Session.GetAliasNames(SourceAlias.Items);
LastSourceAlias := '';
LastDestAlias := '';
end;
{ The next routine is used by the following two - it is not called
From outside this form, so it's not a method of the form - although
it could be... }
Function FindPath(AliasName : String) : String;
var TempStrings : TStringlist;
loop, foundloop, foundpos : integer;
pathstring : string;
begin
PathString := '';
TempStrings := TStringlist.Create;
Session.GetAliasParams(AliasName,TempStrings);
foundloop := -1;
for loop := 0 to TempStrings.Count-1 do begin
foundpos := pos('PATH=',uppercase(TempStrings[loop]));
if foundpos > 0 then begin
pathstring := copy(TempStrings[loop],foundpos+5,255);
foundloop := loop;
end;
end;
TempStrings.Free;
FindPath := PathString;
end;
procedure TDBF2SQL.SourceAliasChange(Sender: TObject);
begin
if (SourceAlias.Text > '') and (LastSourceAlias <> SourceAlias.Text)
then begin
SourcedataBase.Connected := False;
SourceDataBase.AliasName := SourceAlias.Text;
session.GetAliasParams(SourceAlias.Text, SourceDataBase.params);
SourcePath.Text := FindPath(SourceAlias.Text);
if SourcePath.Text = '' then begin
SourcePath.Enabled := True;
end else begin
SourcePath.Enabled := False;
end;
LastSourceAlias := SourceAlias.Text;
SourceDataBase.Connected := True;
end;
LastSourceAlias := SourceAlias.Text;
end;
procedure TDBF2SQL.DestAliasChange(Sender: TObject);
begin
if (DestAlias.Text > '') and (LastDestAlias <> DestAlias.text) then begin;
DestDataBase.Connected := False;
DestDataBase.AliasName := DestAlias.Text;
DestPath.Text := FindPath(DestAlias.Text);
if DestPath.Text = '' then begin
DestPath.Enabled := True;
end else begin
DestPath.Enabled := False;
end;
session.GetAliasParams(DestAlias.Text, DestDataBase.params);
LastDestAlias := DestAlias.Text;
DestDataBase.Connected := True;
end;
LastDestAlias := DestAlias.Text;
end;
{* The Next two routines could probably use a little OO'ing as they
are identical except for field names... }
procedure TDBF2SQL.SourceTableNameEnter(Sender: TObject);
var Inserted : Boolean;
loop : integer;
begin
SourceDataBase.Connected := False;
Inserted := False;
If SourcePath.Enabled then begin
for loop := 0 to SourceDataBase.Params.Count - 1 do
if pos('PATH=',uppercase(SourceDataBase.Params[loop]))
> 0 then begin
Inserted := True;
if SourcePath.Text > '' then
SourceDataBase.Params[loop] := 'PATH='+SourcePath.Text
else
SourceDataBase.Params[loop] := '';
end;
if not Inserted then
SourceDataBase.Params.Add('PATH='+SourcePath.Text);
try
chdir( SourcePath.Text );
except
Application.HandleException( self );
end;
end;
SourceDataBase.Connected := True;
if SourceAlias.Text > '' then
Session.GetTableNames('SOURCEDB', '*.DBF' , True, False, SourceTableName.Items);
end;
procedure TDBF2SQL.DestTableNameEnter(Sender: TObject);
var Inserted : Boolean;
loop : integer;
begin
DestDataBase.Connected := False;
Inserted := False;
If DestPath.Enabled then begin
for loop := 0 to DestDataBase.Params.Count - 1 do
if pos('PATH=',uppercase(DestDataBase.Params[loop]))
> 0 then begin
Inserted := True;
if DestPath.Text > '' then
DestDataBase.Params[loop] := 'PATH='+DestPath.Text
else
DestDataBase.Params[loop] := '';
end;
if not Inserted then
DestDataBase.Params.Add('PATH='+DestPath.Text);
try
chdir( DestPath.Text );
except
Application.HandleException( self );
end;
end;
DestDataBase.Connected := True;
if DestAlias.Text > '' then
Session.GetTableNames('DestDB', '', True, False, DestTableName.Items);
end;
procedure TDBF2SQL.SpeedButton1Click(Sender: TObject);
begin
SourceAliasChange(Self);
DataParams.ParamEdit.Lines := SourceDataBase.params;
if DataParams.ShowModal = mrOK then begin
SourceDataBase.Connected := False;
SourceDataBase.params := DataParams.ParamEdit.Lines;
SourceDataBase.Connected := True;
end;
end;
procedure TDBF2SQL.SpeedButton2Click(Sender: TObject);
begin
DestAliasChange(Self);
DataParams.ParamEdit.Lines := DestDataBase.params;
if DataParams.ShowModal = mrOK then begin
DestDataBase.Connected := False;
DestDataBase.params := DataParams.ParamEdit.Lines;
DestDataBase.Connected := True;
end;
end;
end.